﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace MasMembers.DAL
{
    public class FamilyEntity
    {
        public Int32 Id { get; set; }
        public Int32 Mem_Id { get; set; }
        public string F_Type { get; set; }
        public string F_Name { get; set; }
        public DateTime DOB { get; set; }
        public string Study_Year { get; set; }
        public string School { get; set; }
        public string Educ { get; set; }
        public string F_Job { get; set; }
        public bool Still_Study { get; set; }
        public Int32 Existing { get; set; }

    }

    public class Family
    {
        private static string ConnString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='MasMembers.accdb' ;Jet OLEDB:Database Password=Yarabe11";
        private static string CmdText = "";

        private static FamilyEntity FillEntityFromReader(OleDbDataReader reader)
        {
            FamilyEntity x = new FamilyEntity();
            x.Id = Int32.Parse(reader["Id"].ToString());
            if (reader["F_Name"] != null)
                x.F_Name = reader["F_Name"].ToString();
            if (reader["Mem_Id"] != null)
                x.Mem_Id = Convert.ToInt32(reader["Mem_Id"]);
            if (reader["F_Type"] != null)
                x.F_Type = reader["F_Type"].ToString();
            if (reader["DOB"] != null)
                x.DOB = Convert.ToDateTime(reader["DOB"]);
            if (reader["Study_Year"] != null)
                x.Study_Year = reader["Study_Year"].ToString();
            if (reader["School"] != null)
                x.School = reader["School"].ToString();
            if (reader["Educ"] != null)
                x.Educ = reader["Educ"].ToString();
            if (reader["F_Job"] != null)
                x.F_Job = reader["F_Job"].ToString();
            if (reader["Still_Study"] != null)
                x.Still_Study = Boolean.Parse(reader["Still_Study"].ToString());
            if (reader["Existing"] != null)
                x.Existing = Convert.ToInt32(reader["Existing"]);

            return x;
        }

        public static List<FamilyEntity> GetAllFamily()
        {
            FamilyEntity x;
            List<FamilyEntity> FamilyList = new List<FamilyEntity>();

            CmdText = "Select * from Family ";

            OleDbConnection Con = new OleDbConnection(ConnString);
            OleDbCommand cmd = new OleDbCommand(CmdText, Con);

            try
            {
                Con.Open();

                OleDbDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    x = FillEntityFromReader(reader);
                    FamilyList.Add(x);
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ عند تحديد البيانات المطلوبة من قاعدة البيانات ", "رساله ادارية " + ex.Message);
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                    Con.Close();
            }

            return FamilyList;

        }

        public static List<FamilyEntity> GetFamilyDataByMemId(Int32 Id)
        {
            FamilyEntity x;
            List<FamilyEntity> FamilyList = new List<FamilyEntity>();

            CmdText = "Select * from Family where Mem_Id = ? ";

            OleDbConnection Con = new OleDbConnection(ConnString);
            OleDbCommand cmd = new OleDbCommand(CmdText, Con);

            cmd.Parameters.AddWithValue("@Mem_Id", Id);

            try
            {
                Con.Open();

                OleDbDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    x = FillEntityFromReader(reader);
                    FamilyList.Add(x);
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ عند تحديد البيانات المطلوبة من قاعدة البيانات ", "رساله ادارية " + ex.Message);
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                    Con.Close();
            }

            return FamilyList;
        }

        public static FamilyEntity GetFullFamilyEntity(FamilyEntity P)
        {
            FamilyEntity x = null;

            CmdText = "Select * from Family where (Mem_Id = ?  AND  F_Type = ?  AND  F_Name = ?  AND  DOB = ?  AND  Study_Year = ?  AND  School = ?  AND  Educ = ?  AND  F_Job = ?  AND  Still_Study = ?  AND  Existing = ?) ";

            OleDbConnection Con = new OleDbConnection(ConnString);
            OleDbCommand cmd = new OleDbCommand(CmdText, Con);

            cmd.Parameters.AddWithValue("@Mem_Id", P.Mem_Id);
            cmd.Parameters.AddWithValue("@F_Type", P.F_Type);
            cmd.Parameters.AddWithValue("@F_Name", P.F_Name);
            cmd.Parameters.AddWithValue("@DOB", P.DOB);
            cmd.Parameters.AddWithValue("@Study_Year", P.Study_Year);
            cmd.Parameters.AddWithValue("@School", P.School);
            cmd.Parameters.AddWithValue("@Educ", P.Educ);
            cmd.Parameters.AddWithValue("@F_Job", P.F_Job);
            cmd.Parameters.AddWithValue("@Still_Study", P.Still_Study);
            cmd.Parameters.AddWithValue("@Existing", P.Existing);

            try
            {
                Con.Open();

                OleDbDataReader reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    x = FillEntityFromReader(reader);
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ عند تحديد البيانات المطلوبة من قاعدة البيانات ", "رساله ادارية " + ex.Message);
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                    Con.Close();
            }

            return x;
        }


        public static Int32 Add(FamilyEntity P)
        {
            Int32 Id = -1;

            CmdText = "INSERT INTO Family (Mem_Id,F_Type,F_Name,DOB,Study_Year,School,Educ,F_Job,Still_Study,Existing) values (?,?,?,?,?,?,?,?,?,?)";

            OleDbConnection Con = new OleDbConnection(ConnString);
            OleDbCommand cmd = new OleDbCommand(CmdText, Con);

            cmd.Parameters.AddWithValue("@Mem_Id", P.Mem_Id);
            cmd.Parameters.AddWithValue("@F_Type", P.F_Type);
            cmd.Parameters.AddWithValue("@F_Name", P.F_Name);
            cmd.Parameters.AddWithValue("@DOB", P.DOB);
            cmd.Parameters.AddWithValue("@Study_Year", P.Study_Year);
            cmd.Parameters.AddWithValue("@School", P.School);
            cmd.Parameters.AddWithValue("@Educ", P.Educ);
            cmd.Parameters.AddWithValue("@F_Job", P.F_Job);
            cmd.Parameters.AddWithValue("@Still_Study", P.Still_Study);
            cmd.Parameters.AddWithValue("@Existing", P.Existing);

            try
            {
                Con.Open();
                Int32 NumOfRaws = cmd.ExecuteNonQuery();
                if (NumOfRaws != 0)
                {
                    //FamilyEntity fullEntity = GetFullFamilyEntity(P);
                    Id = 1;
                }
            }
            catch (Exception ex)
            {
                Id = -1;
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                {
                    Con.Close();
                }
            }

            return Id;
        }

        public static bool Update(FamilyEntity P)
        {
            bool flag = true;
            CmdText = "Update Family set Mem_Id = ? , F_Type = ? , F_Name = ? , DOB = ? , Study_Year = ? , School = ? , Educ = ? , F_Job = ? , Still_Study = ? , Existing = ? Where Id = ?";


            OleDbConnection Con = new OleDbConnection(ConnString);
            OleDbCommand cmd = new OleDbCommand(CmdText, Con);

            cmd.Parameters.AddWithValue("@Mem_Id", P.Mem_Id);
            cmd.Parameters.AddWithValue("@F_Type", P.F_Type);
            cmd.Parameters.AddWithValue("@F_Name", P.F_Name);
            cmd.Parameters.AddWithValue("@DOB", P.DOB);
            cmd.Parameters.AddWithValue("@Study_Year", P.Study_Year);
            cmd.Parameters.AddWithValue("@School", P.School);
            cmd.Parameters.AddWithValue("@Educ", P.Educ);
            cmd.Parameters.AddWithValue("@F_Job", P.F_Job);
            cmd.Parameters.AddWithValue("@Still_Study", P.Still_Study);
            cmd.Parameters.AddWithValue("@Existing", P.Existing);
            cmd.Parameters.AddWithValue("@Id", P.Id);


            try
            {
                Con.Open();
                Int32 NumOfRaws = cmd.ExecuteNonQuery();
                if (NumOfRaws == 0)
                    flag = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ فى تغيير البيانات" + ex.Message);
                flag = false;
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                {
                    Con.Close();
                }
            }

            return flag;
        }

        public static bool Delete(int Id)
        {
            bool flag = true;

            CmdText = "delete from Family where Id = ? ";

            OleDbConnection Con = new OleDbConnection(ConnString);
            OleDbCommand cmd = new OleDbCommand(CmdText, Con);

            cmd.Parameters.AddWithValue("@Id", Id);


            try
            {
                Con.Open();
                Int32 NumOfRaws = cmd.ExecuteNonQuery();
                if (NumOfRaws == 0)
                    flag = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ فى حذف البيانات" + ex.Message);
                flag = false;
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                {
                    Con.Close();
                }
            }

            return flag;
        }

    }
}
